﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class MantenimientoUsuarioDA
    Public Function LISTA_USUARIO(ByVal strNombre As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT rownum as numero, XX.* " + _
              " FROM  (select PER.PERSONA , OFZ.descripcion AS OFICINA, " + _
              " PER.OFICINA_ZONAL AS OFICINA2, PER.PPER AS PERPPER, PER.JEFE_INMEDIATO AS JEF,  " + _
              " UPPER(PER.NOMBRES) as NOMBRES  , UPPER(PER.NOMBRES) AS nombre1, PER.TIPO_PERSONA, " + _
              " UPPER(PER.APELLIDO_PATERNO) as APELLIDO_PATERNO,UPPER(PER.APELLIDO_MATERNO) as APELLIDO_MATERNO ,  " + _
              " EJE.DESCRIPCION EJECUTOR,TPER.DESCRIPCION AS TRABAJADOR, " + _
             " OFZ.DESCRIPCION AS OFICINA1,PPER.ABREVIATURA PPER, " + _
              " (PERJF.NOMBRES  || ' ' || PERJF.apellido_paterno|| ' ' ||PERJF.apellido_materno)  AS JEFE ," + _
             "  U.USUARIO, U.USER_NAME, U.PASSWORD, " + _
              " t.descripcion TIPO_USUARIO,  G.DESCRIPCION AS ESTADO,  " + _
             " P.DESCRIPCION perfil,u.PERFIL AS PRF,U.ESTADO AS UST, " + _
              " u.TIPO_USUARIO AS  UTIP,PER.EJECUTOR AS PEREJE  " + _
              " from usuario u  inner join perfil p on u.perfil=p.perfil " + _
              " inner join tipo_usuario t on u.tipo_usuario=t.tipo_usuario  " + _
              " inner join (SELECT CODIGO,DESCRIPCION  " + _
              " FROM(GENERAL) " + _
              "  WHERE ESTADO = 'A' " + _
                                          "   AND ID='ESTADO_USUARIO') g  on U.ESTADO = G.CODIGO " + _
                              " inner join  PERSONA PER ON PER.USUARIO = u.USUARIO  " + _
                              "  left JOIN  EJECUTOR EJE ON PER.EJECUTOR = EJE.EJECUTOR  " + _
                              "  LEFT JOIN PERSONA PERJF ON PER.JEFE_INMEDIATO=PERJF.PERSONA  " + _
                              "  LEFT JOIN  TIPO_PERSONA TPER ON PER.TIPO_PERSONA = TPER.TIPO_PERSONA  " + _
                              "  LEFT JOIN OFICINA_ZONAL OFZ ON PER.OFICINA_ZONAL= OFZ.OFICINA_ZONAL   " + _
                              " LEFT JOIN PPER ON PER.PPER = PPER.PPER  " + _
             " where UPPER(PPER.ABREVIATURA||PER.NOMBRES||PER.APELLIDO_PATERNO) like UPPER('%" & strNombre & "%') " + _
          " order by PER.APELLIDO_PATERNO)   XX "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function LISTA_PERFIL() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try

            lxc_SQL = "select  PERFIL,Upper(DESCRIPCION) as DESCRIPCION  FROM PERFIL order by descripcion asc"

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function LISTA_TIPO_USUARIO() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "select tipo_usuario, descripcion from tipo_usuario "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function LISTA_PASSWORD() As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "select usuario, password from usuario order by usuario "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function OFICINA_ZONAL() As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT OFICINA_ZONAL, DESCRIPCION FROM OFICINA_ZONAL ORDER BY DESCRIPCION"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function ejecutor() As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT EJECUTOR, DESCRIPCION FROM EJECUTOR ORDER BY DESCRIPCION "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function tipo_persona() As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT TIPO_PERSONA,DESCRIPCION FROM TIPO_PERSONA"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Function JEFE() As DataTable

        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "select PER.PERSONA, PER.NOMBRES || ' ' || PER.APELLIDO_PATERNO || ' ' || PER.APELLIDO_MATERNO AS JEFE from persona per inner join usuario u on per.USUARIO = u.USUARIO order by per.NOMBRES "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function
    Public Sub insert_persona_usuario(ByVal per As String, ByVal nom As String, ByVal pater As String, ByVal mater As String, ByVal USU As String, ByVal EJEC As String,
                      ByVal tiper As String, ByVal ofiz As String, ByVal PPER As String, ByVal JEFE As String, ByVal PF As String, ByVal PAS As String,
                      ByVal EST As String, ByVal TPU As String, ByVal USERNAME As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try

            objData.CreateInputOracle("per", per, 20)
            objData.CreateInputOracle("nom", nom, 20)
            objData.CreateInputOracle("pater", pater, 20)
            objData.CreateInputOracle("mater", mater, 20)
            objData.CreateInputOracle("USU", USU, 20)
            objData.CreateInputOracle("EJEC", EJEC, 120)
            objData.CreateInputOracle("tiper", tiper, 20)
            objData.CreateInputOracle("ofiz", ofiz, 20)
            objData.CreateInputOracle("PPER", PPER, 20)
            objData.CreateInputOracle("JEFE", JEFE, 20)
            objData.CreateInputOracle("PF", PF, 20)
            objData.CreateInputOracle("PAS", PAS, 20)
            objData.CreateInputOracle("EST", EST, 20)
            objData.CreateInputOracle("TPU", TPU, 20)
            objData.CreateInputOracle("USERNAME", USERNAME, 20)

            objData.ExecuteOracle_NonQuery("insertPersonaUsuario", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub
    Public Sub editar_persona_usuario(ByVal per As String, ByVal nom As String, ByVal pater As String, ByVal mater As String, ByVal USU As String, ByVal EJEC As String,
                  ByVal tiper As String, ByVal ofiz As String, ByVal PPER As String, ByVal JEFE As String, ByVal PF As String, ByVal PAS As String,
                  ByVal EST As String, ByVal TPU As String, ByVal USERNAME As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try

            objData.CreateInputOracle("per", per, 20)
            objData.CreateInputOracle("nom", nom, 20)
            objData.CreateInputOracle("pater", pater, 20)
            objData.CreateInputOracle("mater", mater, 20)
            objData.CreateInputOracle("USU", USU, 20)
            objData.CreateInputOracle("EJEC", EJEC, 120)
            objData.CreateInputOracle("tiper", tiper, 20)
            objData.CreateInputOracle("ofiz", ofiz, 20)
            objData.CreateInputOracle("PPER2", PPER, 20)
            objData.CreateInputOracle("JEFE", JEFE, 20)
            objData.CreateInputOracle("PF", PF, 20)
            objData.CreateInputOracle("PAS", PAS, 20)
            objData.CreateInputOracle("EST", EST, 20)
            objData.CreateInputOracle("TPU", TPU, 20)
            objData.CreateInputOracle("USERNAM2", USERNAME, 20)

            objData.ExecuteOracle_NonQuery("EDITARPersonaUsuario", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

End Class
